{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4687b2e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import xlwings as xw"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "831ef515",
   "metadata": {},
   "source": [
    "# 读取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51d2af5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "score_1=pd.read_excel(r'C:\\Users\\Admin\\Desktop\\18级高一体测成绩汇总.xls')\n",
    "score_2=pd.read_excel(r'C:\\Users\\Admin\\Desktop\\18级高一体测成绩汇总.xls',sheet_name=1)\n",
    "std = pd.read_excel(r'C:\\Users\\Admin\\Desktop\\体侧成绩评分表.xls',header = [0,1])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "670bd7c1",
   "metadata": {},
   "source": [
    "定义单位转换函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ff3c9e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert(x):\n",
    "    if type(x)==str:\n",
    "        value=round(int(x.split(\"'\")[0])+int(x.split(\"'\")[1])/60,2)\n",
    "        return value\n",
    "    else:\n",
    "        return round(x,2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e08aa1e8",
   "metadata": {},
   "source": [
    "男1000米跑，数据类型是str，并且是4’26这种形式，需要变成float类型的值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b2f23a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "score_1['男1000米跑']=score_1['男1000米跑'].apply(convert)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0bf602cf",
   "metadata": {},
   "source": [
    "评分标准中男1000米跑和女800米跑的成绩都是4‘10’‘这种形式，需要转化为float类型值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a439efc1",
   "metadata": {},
   "outputs": [],
   "source": [
    "std['男1000米跑','成绩']=round(std['男1000米跑','成绩'].str.extract(r'(\\d+)[\\'](\\d+)[\\\"]')[0].astype('int')\n",
    "      +std['男1000米跑','成绩'].str.extract(r'(\\d+)[\\'](\\d+)[\\\"]')[1].astype('int')/100,2)\n",
    "\n",
    "std['女800米跑','成绩']=round(std['女800米跑','成绩'].str.extract(r'(\\d+)[\\'](\\d+)[\\\"]')[0].astype('int')\n",
    "      +std['女800米跑','成绩'].str.extract(r'(\\d+)[\\'](\\d+)[\\\"]')[1].astype('int')/100,2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8b15ed2",
   "metadata": {},
   "source": [
    "其他所有数值类型的值，都要转换为float类型的值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "600be930",
   "metadata": {},
   "outputs": [],
   "source": [
    "score_1.iloc[:,2:]=score_1.iloc[:,2:].astype('float')\n",
    "score_2.iloc[:,2:]=score_2.iloc[:,2:].astype('float')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "743daf36",
   "metadata": {},
   "source": [
    "5、对体测成绩进行分数转换，跑步类（越小越好）；跳远、体前屈（越大越好）\n",
    "\n",
    " 使用map、apply、transform方法\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16643a80",
   "metadata": {},
   "outputs": [],
   "source": [
    "score_1['男1000米跑分数']=score_1['男1000米跑'].apply(lambda x:max(std['男1000米跑']['分数'][std['男1000米跑']['成绩']>=x]) \n",
    "                                              if x !=0 and max(std['男1000米跑']['成绩'])>x else 0)\n",
    "score_1['男50米跑分数']=score_1['男50米跑'].apply(lambda x:max(std['男50米跑']['分数'][std['男50米跑']['成绩']>=x]) \n",
    "                                              if x !=0 and max(std['男50米跑']['成绩'])>x else 0)\n",
    "\n",
    "score_1['男跳远分数']=score_1['男跳远'].apply(lambda x:max(std['男跳远']['分数'][std['男跳远']['成绩']<=x]) \n",
    "                                              if x !=0 and min(std['男跳远']['成绩'])<x else 0)\n",
    "score_1['男体前屈分数']=score_1['男体前屈'].apply(lambda x:max(std['男体前屈']['分数'][std['男体前屈']['成绩']<=x]) \n",
    "                                              if x !=0 and min(std['男体前屈']['成绩'])<x else 0)\n",
    "score_1['男引体分数']=score_1['男引体'].apply(lambda x:max(std['男引体']['分数'][std['男引体']['成绩']<=x]) \n",
    "                                              if x !=0 and min(std['男引体']['成绩'])<x else 0)\n",
    "score_1['男肺活量分数']=score_1['男肺活量'].apply(lambda x:max(std['男肺活量']['分数'][std['男肺活量']['成绩']<=x]) \n",
    "                                              if x !=0 and min(std['男肺活量']['成绩'])<x else 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "588ea987",
   "metadata": {},
   "source": [
    "同理，女生分数如下"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f603a536",
   "metadata": {},
   "outputs": [],
   "source": [
    "score_2['女800米跑分数']=score_2['女800米跑'].apply(lambda x:max(std['女800米跑']['分数'][std['女800米跑']['成绩']>=x]) \n",
    "                                              if x !=0 and max(std['女800米跑']['成绩'])>x else 0)\n",
    "score_2['女50米跑分数']=score_2['女50米跑'].apply(lambda x:max(std['女50米跑']['分数'][std['女50米跑']['成绩']>=x]) \n",
    "                                              if x !=0 and max(std['女50米跑']['成绩'])>x else 0)\n",
    "\n",
    "score_2['女跳远分数']=score_2['女跳远'].apply(lambda x:max(std['女跳远']['分数'][std['女跳远']['成绩']<=x]) \n",
    "                                              if x !=0 and min(std['女跳远']['成绩'])<x else 0)\n",
    "score_2['女体前屈分数']=score_2['女体前屈'].apply(lambda x:max(std['女体前屈']['分数'][std['女体前屈']['成绩']<=x]) \n",
    "                                              if x !=0 and min(std['女体前屈']['成绩'])<x else 0)\n",
    "score_2['女仰卧分数']=score_2['女仰卧'].apply(lambda x:max(std['女仰卧']['分数'][std['女仰卧']['成绩']<=x]) \n",
    "                                              if x !=0 and min(std['女仰卧']['成绩'])<x else 0)\n",
    "score_2['女肺活量分数']=score_2['女肺活量'].apply(lambda x:max(std['女肺活量']['分数'][std['女肺活量']['成绩']<=x]) \n",
    "                                              if x !=0 and min(std['女肺活量']['成绩'])<x else 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c84507cf",
   "metadata": {},
   "source": [
    "列索引重排"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8904926c",
   "metadata": {},
   "outputs": [],
   "source": [
    "score_1=score_1[['班级','性别','男1000米跑','男1000米跑分数','男50米跑','男50米跑分数','男跳远','男跳远分数','男体前屈','男体前屈分数',\n",
    "                 '男引体','男引体分数','男肺活量','男肺活量分数','身高','体重','BMI']]\n",
    "\n",
    "score_2=score_2[['班级','性别','女800米跑','女800米跑分数','女50米跑','女50米跑分数','女跳远','女跳远分数','女体前屈','女体前屈分数',\n",
    "                 '女仰卧','女仰卧分数','女肺活量','女肺活量分数','身高','体重','BMI']]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.4"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "169.261px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
